#!/usr/bin/env python3
#-*- coding: utf-8 -*-

"""
封装SQLITE3的操作
"""

import sqlite3
from contextlib import contextmanager
from config import DEBUG_VALUE
import logging
import json



class SQLiteDB:
    def __init__(self, db_path=':memory:'):
        """初始化数据库连接
        :param db_path: 数据库文件路径，默认为内存数据库
        """
        self.db_path = db_path

    @contextmanager
    def _get_cursor(self):
        """获取游标的上下文管理器"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        try:
            yield cursor
            conn.commit()
        except Exception as e:
            print(f"\033[33m  执行异常 {e} \033[0m")
            conn.rollback()
            raise e
        finally:
            cursor.close()
            conn.close()

    def execute(self, sql, params=None):
        """执行SQL语句
        :param sql: SQL语句
        :param params: 参数列表或字典
        :return: 受影响的行数
        """
        with self._get_cursor() as cursor:
            if params:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
            return cursor.rowcount

    def fetch_one(self, sql, params=None):
        """查询单条记录
        :param sql: SQL查询语句
        :param params: 参数列表或字典
        :return: 单条记录或None
        """
        with self._get_cursor() as cursor:
            if params:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
            return cursor.fetchone()

    def fetch_all(self, sql, params=None):
        """查询所有记录
        :param sql: SQL查询语句
        :param params: 参数列表或字典
        :return: 所有记录的列表
        """
        with self._get_cursor() as cursor:
            if params:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)

            return cursor.fetchall()

    def create_table(self, table_name, columns):
        """创建表
        :param table_name: 表名
        :param columns: 列定义字典，如 {'id': 'INTEGER PRIMARY KEY', 'name': 'TEXT'}
        """
        columns_sql = ', '.join([f"{k} {v}" for k, v in columns.items()])
        sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_sql})"
        #f'CREATE TABLE {table_name} ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,"time" text,"db" TEXT)'
        self.execute(sql)
        if DEBUG_VALUE:
            print('create_table sql : '+sql)

    def insert_one(self, table_name, data):
        """插入数据
        :param table_name: 表名
        :param data: 数据字典，如 {'name': 'Alice', 'age': 25}
        """
        self.execute(f'INSERT INTO {table_name} (time,db) VALUES (?,?)',  data)

    def insert_many(self, table_name, data):
        """插入数据
        :param table_name: 表名
        :param data: 数据
        """
        for i in data:
            self.execute(f'INSERT INTO {table_name} (time,db) VALUES (?,?)', (i[0], i[1]))
        print("insert_many 保存...")

    # 获取所有表名
    def select_tables(self, sql, params=None):
        """查询所有记录
        :param sql: SQL查询语句
        :param params: 参数列表或字典
        :return: 所有记录的列表
        """
        with self._get_cursor() as cursor:
            if 1:
                cursor.execute(sql)
                res = cursor.fetchall()
                print(res)
                for i in res:
                    print(i[0])

    def close(self):
        """关闭数据库连接"""
        pass



if __name__ == '__main__':
    db = SQLiteDB('../data/fenbei_monitor.db')
    db.select_tables("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
